CREATE DATABASE KCDB
GO
USE KCDB
GO
CREATE TABLE ZZB
(
    ZZH CHAR(4) PRIMARY KEY,
    ZZM NVARCHAR(5) UNIQUE,
)
GO
CREATE TABLE QKB
(
    QKH CHAR(4) PRIMARY KEY,
    QKM VARCHAR(20) NOT NULL,
    LB  CHAR(2) CHECK (LB = '是' OR LB = '否'),
    BMF INT,
)
GO
CREATE TABLE TGB
(
    ZZH  CHAR(4)       NOT NULL,
    QKH  CHAR(4)       NOT NULL,
    TGRQ SMALLDATETIME NOT NULL                          DEFAULT (GETDATE()),
    WZMC CHAR(50),
    SGJG NVARCHAR(3) CHECK (SGJG = '通过' OR SGJG = '未通过') DEFAULT ('未通过'),
    PRIMARY KEY (ZZH, QKH, TGRQ),
    FOREIGN KEY (ZZH) REFERENCES ZZB (ZZH),
    FOREIGN KEY (QKH) REFERENCES QKB (QKH),
)
GO

-- （1）查询2006年以后（包括2006年）的投稿情况，列出作者名、期刊名、文章名称和投稿日期。
SELECT ZZM, QKM, WZMC, TGRQ
    FROM TGB INNER JOIN QKB ON QKB.QKH = TGB.QKH
             INNER JOIN ZZB ON ZZB.ZZH = TGB.ZZH
    WHERE TGRQ >= 2006
-- （2）查询姓哪个姓的作者最多，列出这个姓氏和姓这个姓氏的作者人数，包含并列的情况。
SELECT SUBSTRING(ZZM, 0, 2) AS 姓, COUNT(SUBSTRING(ZZM, 0, 2)) AS 人数
    FROM ZZB
    GROUP BY SUBSTRING(ZZM, 0, 2)
    ORDER BY 人数 DESC
-- （3）查询作者“杨伍华”没有投过稿的核心期刊的名称。
SELECT QKM
    FROM QKB
    WHERE QKH NOT IN
          (
              SELECT QKH
                  FROM TGB INNER JOIN ZZB ON ZZB.ZZH = TGB.ZZH
                  WHERE ZZM = '杨伍华'
          )
-- （4）查询“杨伍华”投过稿的期刊的投稿情况，显示期刊号，审稿通过总次数，版面费总金额（说明:只有审稿结果为"通过"时,作者才需向期刊缴纳相应版面费)，要求只列出投稿次数超过5次(不包括5次)且版面费总金额大于7000的情况。
SELECT TGB.QKH, COUNT(TGB.QKH) AS 通过次数, SUM(QKB.BMF) AS 版面费
    FROM TGB INNER JOIN QKB ON QKB.QKH = TGB.QKH
             INNER JOIN ZZB ON ZZB.ZZH = TGB.ZZH
    WHERE TGB.ZZH IN
          (
              SELECT TGB.ZZH
                  FROM TGB INNER JOIN ZZB ON ZZB.ZZH = TGB.ZZH
                  WHERE ZZM = '杨伍华' AND SGJG = '通过'
                  GROUP BY TGB.ZZH
                  HAVING COUNT(TGB.ZZH) > 5
          )
    GROUP BY TGB.ZZH, TGB.QKH, BMF
    HAVING SUM(BMF) > 7000
-- （5）查询全体作者（包括还没有投过稿的作者）的人均投稿次数，列出总投稿次数、总人数和人均投稿次数。要求平均投稿次数保存到小数点后2位。
SELECT COUNT(TGB.ZZH)                                                        AS 总投稿次数,
       COUNT(DISTINCT ZZB.ZZH)                                               AS 总人数,
       CAST(COUNT(TGB.ZZH) * 1.0 / COUNT(DISTINCT ZZB.ZZH) AS NUMERIC(3, 2)) AS 人均投稿次数
    FROM TGB INNER JOIN ZZB ON ZZB.ZZH = TGB.ZZH
-- （6）向投稿表中插入一条记录,作者号为“zz01”,期刊号为“qk02”,文章名称为“地铁限界系统的研究”，投稿日期和审稿结果为默认值。
INSERT INTO TGB(ZZH, QKH, WZMC) VALUES ('zz01', 'qk02', '地铁限界系统的研究')
-- （7）删除投稿表中2000年以前（包括2000年），期刊名为"哲学研究"中审稿未通过的的投稿记录。
DELETE
    FROM TGB
    WHERE TGRQ <= 2000
      AND QKH IN (
                     SELECT TGB.QKH
                         FROM TGB INNER JOIN QKB ON QKB.QKH = TGB.QKH
                         WHERE QKM = '哲学研究' AND SGJG = '未通过'
                 )
-- （8）统计每位作者的投稿情况，列出作者名，投稿通过率（保留到小数点后2位），评价。其中，当投 稿通过率高于0.7，评价为“高”；当投稿通过率在0.4到0.7之间，评价为“中”；当投稿通过率低于0.4，评价为“低”。（可分步骤实现）

CREATE VIEW TGLB(ZZH, TGL) AS
SELECT TGB.ZZH,
       CAST((SUM((
           CASE
               WHEN TGB.SGJG = '通过' THEN 1
               WHEN TGB.SGJG = '未通过' THEN 0
               END
           )) * 1.0 / COUNT(TGB.QKH)) AS NUMERIC(3, 2)) AS TGL
    FROM TGB
    GROUP BY TGB.ZZH;

SELECT ZZH,
       TGL,
       (
           CASE
               WHEN TGL > 0.7 THEN '高'
               WHEN TGL >= 0.4 AND TGL <= 0.7 THEN '中'
               WHEN TGL < 0.4 THEN '低'
               END
           )
           AS 评价
    FROM TGLB;

-- （9）查询投了作者‘杨伍华’所投所有期刊的作者名。
SELECT DISTINCT ZZM
    FROM TGB AS TGB1 INNER JOIN ZZB AS ZZB1 ON ZZB1.ZZH = TGB1.ZZH
    WHERE TGB1.ZZH = ZZB1.ZZH
      AND NOT EXISTS(
            SELECT *
                FROM TGB AS TGB2 INNER JOIN ZZB AS ZZB2 ON ZZB1.ZZH = TGB1.ZZH
                WHERE ZZB2.ZZM = '杨伍华'
                  AND TGB2.ZZH = ZZB2.ZZH
                  AND NOT EXISTS(
                        SELECT *
                            FROM TGB AS TGB3 INNER JOIN ZZB AS ZZB3 ON ZZB1.ZZH = TGB1.ZZH
                            WHERE TGB3.ZZH = ZZB3.ZZH AND TGB3.QKH = TGB1.QKH AND TGB3.ZZH = TGB2.ZZH
                    )
        )

-- （10）使用游标实现将“计算机学报”审稿未通过的最后一个作者的审稿结果改为“通过”。

DECLARE CUR SCROLL CURSOR FOR
    SELECT *
        FROM TGB INNER JOIN QKB ON QKB.QKH = TGB.QKH
        WHERE QKB.QKM = '计算机学报' AND SGJG = '未通过'
OPEN CUR
FETCH ABSOLUTE -1 FROM CUR
UPDATE TGB SET SGJG='通过' WHERE CURRENT OF CUR
CLOSE CUR
DEALLOCATE CUR


-- 三．建立教师表teacher(tid(教师编号)， tname(教师姓名)，职称（job），salary(工资))，请为teacher表设计一触发器，实现完整性规则“教授的工资不得低于8000元，如果低于8000元，自动改为8000元”，并显示“※※（教师姓名）教师工资改为※※（修改后工资）元”的提示信息。输入若干数据，验证触发器的执行。

CREATE TABLE TEACHER
(
    TID    CHAR(10) PRIMARY KEY,
    TNAME  NCHAR(10) NOT NULL,
    JOB    NCHAR(10),
    SALARY INT,
)

CREATE TRIGGER "教授的工资不得低于8000元，如果低于8000元，自动改为8000元"
    ON TEACHER
    AFTER UPDATE, INSERT
    AS
BEGIN
    DECLARE @TID CHAR(10)
    DECLARE @TNAME NCHAR(10)
    DECLARE "CUR_XS" CURSOR FOR SELECT "TID", "TNAME" FROM INSERTED WHERE SALARY < 8000 AND JOB = '教授'
    OPEN "CUR_XS"
    FETCH NEXT FROM "CUR_XS" INTO @TID,@TNAME
    WHILE @@FETCH_STATUS = 0
        BEGIN
            UPDATE TEACHER SET SALARY=8000 WHERE TID = @TID
            PRINT @TNAME + '教师工资改为8000元';
            FETCH NEXT FROM "CUR_XS" INTO @TID,@TNAME
        END
    CLOSE "CUR_XS"
    DEALLOCATE "CUR_XS"
END
GO
--示例
INSERT
    INTO KCDB.DBO.TEACHER (TID, TNAME, JOB, SALARY)
    VALUES ('100', 'aasaa', '教授', 1),
           ('1000', 'ASD', 'aa', 1),
           ('10000', 'ASDDFDD', '教授', 9000)


-- 借书表：lend（学号，索书号，借书日期，应还日期，是否续借）
-- 欠款表：student(学号，日期，欠款金额)
-- 还书表：return(学号，索书号，还书日期)
-- 并输入若干数据。
-- 请设计一个存储过程实现还书操作，要求还书时删除lend表内的借阅记录，并向还书表中插入一条还书记录，注意还书日期为当前日期，并且根据应还日期和当前日期来判断该书是否超期，如果超期按照超期的天数计算出罚款金额（每天每本书罚款0.1元），并将罚款信息插入到student表中。

CREATE TABLE LEND
(
    学号   INT      NOT NULL                                  DEFAULT 1,
    索书号  INT      NOT NULL,
    借书日期 DATETIME NOT NULL                                  DEFAULT GETDATE(),
    应还日期 DATETIME NOT NULL                                  DEFAULT GETDATE(),
    是否续借 CHAR(2)  NOT NULL CHECK (是否续借 = '是' OR 是否续借 = '否') DEFAULT '否',
    CONSTRAINT LEND_PK
        PRIMARY KEY (学号, 索书号, 借书日期)
)
GO
CREATE TABLE "RETURN"
(
    学号   INT      NOT NULL,
    日期   DATETIME NOT NULL,
    欠款金额 FLOAT    NOT NULL,
    CONSTRAINT RETURN_PK
        PRIMARY KEY (学号, 日期)
)
GO
CREATE TABLE STUDENT
(
    学号   INT      NOT NULL,
    索书号  INT      NOT NULL,
    还书日期 DATETIME NOT NULL,
    CONSTRAINT STUDENT_PK
        PRIMARY KEY (学号, 索书号, 还书日期)
)

CREATE PROCEDURE 还书(@索书号 INT) AS
BEGIN
    DECLARE @学号 INT
    DECLARE @应还日期 DATETIME
    SELECT @学号 = 学号, @应还日期 = 应还日期 FROM LEND WHERE 索书号 = @索书号
    DELETE FROM LEND WHERE 索书号 = @索书号
    INSERT INTO "STUDENT"(学号, 索书号, 还书日期) VALUES (@学号, @索书号, GETDATE())
    IF (GETDATE() > @应还日期)
        BEGIN
            INSERT
                INTO "RETURN"(学号, 日期, 欠款金额)
                VALUES (@学号, GETDATE(), CONVERT(FLOAT, DATEDIFF(DAY, @应还日期, GETDATE())) * 0.1)
        END
END
INSERT INTO KCDB.DBO.LEND (学号, 索书号, 借书日期, 应还日期, 是否续借) VALUES (DEFAULT, 1, DEFAULT, DEFAULT, DEFAULT)
INSERT INTO KCDB.DBO.LEND (学号, 索书号, 借书日期, 应还日期, 是否续借) VALUES (DEFAULT, 2, DEFAULT, DEFAULT, DEFAULT)
GO

EXEC 还书 1
GO

--hospital数据库
CREATE DATABASE HOSPITAL
USE HOSPITAL
GO
--医生表
CREATE TABLE T
(
    TNO    INT PRIMARY KEY,
    TNAME  NVARCHAR(10),
    TSEX   CHAR(2),
    TBIRTH DATETIME,
    KNAME  NVARCHAR(20),
    TPH    CHAR(20)
)
--病人表
CREATE TABLE B
(
    BNO    INT PRIMARY KEY,
    BNAME  NVARCHAR(10),
    BSEX   CHAR(2),
    BBIRTH DATETIME,
    TPH    CHAR(20)
)
--药品表
CREATE TABLE M
(
    MNO    INT PRIMARY KEY,
    MNAME  NVARCHAR(30),
    MST    CHAR(10),
    MPRICE FLOAT,
    MDATE  DATETIME,
    MEX    INT
)
--处方表
CREATE TABLE C
(
    CNO   INT PRIMARY KEY,
    TNO   INT REFERENCES T (TNO),
    BNO   INT REFERENCES B (BNO),
    CDATE DATETIME,
    CZJ   FLOAT
)
--药品处方细节表
CREATE TABLE CM
(
    CNO    INT REFERENCES C (CNO),
    MNO    INT REFERENCES M (MNO),
    MQU    INT,
    MPRICE FLOAT,
    PRIMARY KEY (CNO, MNO)
)
--检查处方细节表
CREATE TABLE CC
(
    CNO   INT REFERENCES C (CNO),
    PNAME CHAR(30),
    TNO   INT REFERENCES T (TNO),
    PRE   CHAR(50),
    PRIMARY KEY (CNO, PNAME)
)
--检查项目表
CREATE TABLE P
(
    PNAME  CHAR(30) PRIMARY KEY,
    PPRICE FLOAT
)
--将要过期和已过期的药品表
CREATE TABLE OM
(
    MNO   INT PRIMARY KEY,
    ODATE DATETIME,
    ODAYS INT
)


-- 1.查询检查过所有检查项目的病人的编号和姓名。
SELECT BNO, BNAME
    FROM B AS B1
    WHERE NOT EXISTS(SELECT *
                         FROM P AS P2
                         WHERE NOT EXISTS(SELECT *
                                              FROM C AS C3 INNER JOIN CC AS CC3 ON C3.CNO = CC3.CNO
                                              WHERE C3.BNO = B1.BNO AND CC3.PNAME = P2.PNAME
                             )
        )
-- 2.检索 ‘李明’医生没有开过处方的药品名称。
SELECT MNAME
    FROM M
    WHERE MNO NOT IN (
                         SELECT MNO
                             FROM T INNER JOIN C ON T.TNO = C.TNO
                                    INNER JOIN CM ON C.CNO = CM.CNO
                             WHERE TNAME = '李明'
                     )
-- 3.查询2019年每个月使用量最高的药品编号和药品名称，要求输出月份，药品编号，药品名称以及使用数量。
-- @formatter:off
SELECT TOP 1 MONTH(CDATE) AS 月份, M.MNO AS 药品编号, MNAME AS 药品名称, SUM(MQU) AS 数量 FROM M INNER JOIN CM ON M.MNO = CM.MNO INNER JOIN C ON CM.CNO = C.CNO WHERE YEAR(CDATE) = 2019 AND MONTH(CDATE) = 1 GROUP BY MONTH(CDATE), M.MNO, MNAME UNION
SELECT TOP 1 MONTH(CDATE) AS 月份, M.MNO AS 药品编号, MNAME AS 药品名称, SUM(MQU) AS 数量 FROM M INNER JOIN CM ON M.MNO = CM.MNO INNER JOIN C ON CM.CNO = C.CNO WHERE YEAR(CDATE) = 2019 AND MONTH(CDATE) = 2 GROUP BY MONTH(CDATE), M.MNO, MNAME UNION
SELECT TOP 1 MONTH(CDATE) AS 月份, M.MNO AS 药品编号, MNAME AS 药品名称, SUM(MQU) AS 数量 FROM M INNER JOIN CM ON M.MNO = CM.MNO INNER JOIN C ON CM.CNO = C.CNO WHERE YEAR(CDATE) = 2019 AND MONTH(CDATE) = 3 GROUP BY MONTH(CDATE), M.MNO, MNAME UNION
SELECT TOP 1 MONTH(CDATE) AS 月份, M.MNO AS 药品编号, MNAME AS 药品名称, SUM(MQU) AS 数量 FROM M INNER JOIN CM ON M.MNO = CM.MNO INNER JOIN C ON CM.CNO = C.CNO WHERE YEAR(CDATE) = 2019 AND MONTH(CDATE) = 4 GROUP BY MONTH(CDATE), M.MNO, MNAME UNION
SELECT TOP 1 MONTH(CDATE) AS 月份, M.MNO AS 药品编号, MNAME AS 药品名称, SUM(MQU) AS 数量 FROM M INNER JOIN CM ON M.MNO = CM.MNO INNER JOIN C ON CM.CNO = C.CNO WHERE YEAR(CDATE) = 2019 AND MONTH(CDATE) = 5 GROUP BY MONTH(CDATE), M.MNO, MNAME UNION
SELECT TOP 1 MONTH(CDATE) AS 月份, M.MNO AS 药品编号, MNAME AS 药品名称, SUM(MQU) AS 数量 FROM M INNER JOIN CM ON M.MNO = CM.MNO INNER JOIN C ON CM.CNO = C.CNO WHERE YEAR(CDATE) = 2019 AND MONTH(CDATE) = 6 GROUP BY MONTH(CDATE), M.MNO, MNAME UNION
SELECT TOP 1 MONTH(CDATE) AS 月份, M.MNO AS 药品编号, MNAME AS 药品名称, SUM(MQU) AS 数量 FROM M INNER JOIN CM ON M.MNO = CM.MNO INNER JOIN C ON CM.CNO = C.CNO WHERE YEAR(CDATE) = 2019 AND MONTH(CDATE) = 7 GROUP BY MONTH(CDATE), M.MNO, MNAME UNION
SELECT TOP 1 MONTH(CDATE) AS 月份, M.MNO AS 药品编号, MNAME AS 药品名称, SUM(MQU) AS 数量 FROM M INNER JOIN CM ON M.MNO = CM.MNO INNER JOIN C ON CM.CNO = C.CNO WHERE YEAR(CDATE) = 2019 AND MONTH(CDATE) = 8 GROUP BY MONTH(CDATE), M.MNO, MNAME UNION
SELECT TOP 1 MONTH(CDATE) AS 月份, M.MNO AS 药品编号, MNAME AS 药品名称, SUM(MQU) AS 数量 FROM M INNER JOIN CM ON M.MNO = CM.MNO INNER JOIN C ON CM.CNO = C.CNO WHERE YEAR(CDATE) = 2019 AND MONTH(CDATE) = 9 GROUP BY MONTH(CDATE), M.MNO, MNAME UNION
SELECT TOP 1 MONTH(CDATE) AS 月份, M.MNO AS 药品编号, MNAME AS 药品名称, SUM(MQU) AS 数量 FROM M INNER JOIN CM ON M.MNO = CM.MNO INNER JOIN C ON CM.CNO = C.CNO WHERE YEAR(CDATE) = 2019 AND MONTH(CDATE) = 10 GROUP BY MONTH(CDATE), M.MNO, MNAME UNION
SELECT TOP 1 MONTH(CDATE) AS 月份, M.MNO AS 药品编号, MNAME AS 药品名称, SUM(MQU) AS 数量 FROM M INNER JOIN CM ON M.MNO = CM.MNO INNER JOIN C ON CM.CNO = C.CNO WHERE YEAR(CDATE) = 2019 AND MONTH(CDATE) = 11 GROUP BY MONTH(CDATE), M.MNO, MNAME UNION
SELECT TOP 1 MONTH(CDATE) AS 月份, M.MNO AS 药品编号, MNAME AS 药品名称, SUM(MQU) AS 数量 FROM M INNER JOIN CM ON M.MNO = CM.MNO INNER JOIN C ON CM.CNO = C.CNO WHERE YEAR(CDATE) = 2019 AND MONTH(CDATE) = 12 GROUP BY MONTH(CDATE), M.MNO, MNAME
-- @formatter:on
SELECT 月份, 药品编号, 药品名称, 数量
    FROM (
             SELECT MONTH(CDATE)                                                   AS 月份,
                    M.MNO                                                          AS 药品编号,
                    MNAME                                                          AS 药品名称,
                    SUM(MQU)                                                       AS 数量,
                    RANK() OVER (PARTITION BY MONTH(CDATE) ORDER BY SUM(MQU) DESC) AS RANK1 --rank()对个月按总数进行排序
                 FROM M INNER JOIN CM ON M.MNO = CM.MNO
                        INNER JOIN C ON CM.CNO = C.CNO
                 WHERE YEAR(CDATE) = 2019
                 GROUP BY MONTH(CDATE), M.MNO, MNAME
         ) A
    WHERE A.RANK1 = 1
-- 4.为处方表添加约束：医生编号为外键，处方日期添加默认值为当前日期。
-- ALTER TABLE C
--     ADD CONSTRAINT FK_TNO FOREIGN KEY (TNO) REFERENCES T (TNO)
ALTER TABLE C
    ADD CONSTRAINT DEFAULT_CDATE DEFAULT GETDATE() FOR CDATE
-- 5.将每个处方中的：处方编号，药品总数量，药品总价格，检查项目总数量，检查项目总价格 创建一个视图。
CREATE VIEW "处方编号，药品总数量，药品总价格，检查项目总数量，检查项目总价格"(处方编号, 药品总数量, 药品总价格, 检查项目总数量, 检查项目总价格)
AS
SELECT YP.处方编号, 药品总数量, 药品总价格, 检查项目总数量, 检查项目总价格
    FROM (
             SELECT C.CNO AS 处方编号, ISNULL(SUM(MQU), 0) AS 药品总数量, ISNULL(SUM(MQU * MPRICE), 0) AS 药品总价格
                 FROM C LEFT JOIN CM ON C.CNO = CM.CNO
                 GROUP BY C.CNO
         ) YP,
         (
             SELECT C.CNO AS 处方编号, COUNT(P.PNAME) AS 检查项目总数量, ISNULL(SUM(PPRICE), 0) AS 检查项目总价格
                 FROM C LEFT JOIN CC ON C.CNO = CC.CNO
                        LEFT JOIN P ON P.PNAME = CC.PNAME
                 GROUP BY C.CNO
         ) XM
    WHERE YP.处方编号 = XM.处方编号
-- 6.查询2017年每个科室每个医生每个月的处方总金额，要求显示科室名称，医生名称，月份，总金额。
SELECT KNAME, TNAME, MONTH(CDATE), 药品总价格 + 检查项目总价格 AS 总金额
    FROM T LEFT JOIN C ON T.TNO = C.TNO,
         (
             SELECT YP.处方编号, 药品总数量, 药品总价格, 检查项目总数量, 检查项目总价格
                 FROM (
                          SELECT C.CNO AS 处方编号, ISNULL(SUM(MQU), 0) AS 药品总数量, ISNULL(SUM(MQU * MPRICE), 0) AS 药品总价格
                              FROM C LEFT JOIN CM ON C.CNO = CM.CNO
                              GROUP BY C.CNO
                      ) YP,
                      (
                          SELECT C.CNO AS 处方编号, COUNT(P.PNAME) AS 检查项目总数量, ISNULL(SUM(PPRICE), 0) AS 检查项目总价格
                              FROM C LEFT JOIN CC ON C.CNO = CC.CNO
                                     LEFT JOIN P ON P.PNAME = CC.PNAME
                              GROUP BY C.CNO
                      ) XM
                 WHERE YP.处方编号 = XM.处方编号
         ) AAA
    WHERE C.CNO = AAA.处方编号 AND YEAR(CDATE) = 2017
    GROUP BY KNAME, TNAME, MONTH(CDATE), 药品总价格, 检查项目总价格
-- 7.查询每个医生看过的病人中男女病人的人数，要求输出：医生编号，男病人人数，女病人人数。
SELECT T.TNO                                                  AS 医生编号,
       ISNULL(SUM(CASE WHEN BSEX = '男' THEN 1 ELSE 0 END), 0) AS 男病人人数,
       ISNULL(SUM(CASE WHEN BSEX = '女' THEN 1 ELSE 0 END), 0) AS 女病人人数
    FROM T LEFT JOIN C ON T.TNO = C.TNO
           LEFT JOIN B ON C.BNO = B.BNO
    GROUP BY T.TNO
-- 8.创建触发器实现：向药品处方表或者检查项目处方表中插入处方细节记录时，通过触发器检查药品编号的值在药品表中是否存在，同时还要检查检查项目的值在检查项目表中是否存在，若不存在，则取消插入，否则插入成功，如果插入成功则计算该处方的总价格插入到处方表中。
CREATE TRIGGER TRYP
    ON CM
    FOR INSERT
    AS
BEGIN
    IF EXISTS(
            SELECT *
                FROM INSERTED
                WHERE MNO NOT IN (
                                     SELECT MNO
                                         FROM M
                                 )
                   OR CNO NOT IN (
                                     SELECT CNO
                                         FROM C
                                 )
        )
        BEGIN
            ROLLBACK
        END
    ELSE
        BEGIN
            UPDATE C
            SET CZJ=CZJ + (
                              SELECT SUM(MQU * MPRICE)
                                  FROM INSERTED
                                  WHERE INSERTED.CNO = C.CNO
                          )
        END
END
GO

CREATE TRIGGER TRJC
    ON CC
    FOR INSERT
    AS
BEGIN
    IF EXISTS(
            SELECT *
                FROM INSERTED
                WHERE PNAME NOT IN (
                                       SELECT PNAME
                                           FROM P
                                   )
                   OR CNO NOT IN (
                                     SELECT CNO
                                         FROM C
                                 )
        )
        BEGIN
            ROLLBACK
        END
    ELSE
        BEGIN
            UPDATE C
            SET CZJ=CZJ + (
                              SELECT SUM(PPRICE)
                                  FROM INSERTED INNER JOIN P ON P.PNAME = INSERTED.PNAME
                                  WHERE INSERTED.CNO = C.CNO
                          )
        END
END
GO

-- 9.请设计一个存储过程实现下列功能：查询每种药品是否快到保质期，将保质期只剩少于半年的药品插入到将要过期和已过期药品表中，如果已经超过保质期，则将该药品从药品表中删除。
CREATE PROC "查询每种药品是否快到保质期，将保质期只剩少于半年的药品插入到将要过期和已过期药品表中，如果已经超过保质期，则将该药品从药品表中删除。"
AS
BEGIN
    DECLARE @MNO CHAR(10), @ODATE DATETIME, @ODAYS INT
    DECLARE CUR SCROLL CURSOR FOR
        SELECT MNO, DATEADD(MM, MEX, MDATE), DATEDIFF(DD, GETDATE(), DATEADD(MM, MEX, MDATE))
            FROM M
            WHERE DATEDIFF(MM, GETDATE(), DATEADD(MM, MEX, MDATE)) < 6
    OPEN CUR
    FETCH NEXT FROM CUR INTO @MNO,@ODATE,@ODAYS
    WHILE @@FETCH_STATUS = 0
        BEGIN
            INSERT
                INTO OM
                VALUES (@MNO, @ODATE, @ODAYS)
            FETCH NEXT FROM CUR INTO @MNO,@ODATE,@ODAYS
        END
    CLOSE CUR
    DEALLOCATE CUR
    DELETE
        FROM M
        WHERE DATEDIFF(MM, MDATE, GETDATE()) > MEX
END
GO
-- 10.使用游标实现将2019年使用量最多的前五种药品的单价价格加上1元。
DECLARE CUR SCROLL CURSOR
    FOR
    SELECT TOP 5 WITH TIES MNO
        FROM CM INNER JOIN C ON CM.CNO = C.CNO
        WHERE YEAR(CDATE) = 2019
        GROUP BY MNO
        ORDER BY SUM(MQU) DESC
OPEN CUR
DECLARE @MNO INT
FETCH NEXT FROM CUR INTO @MNO
WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE M
        SET MPRICE=MPRICE + 1
            WHERE MNO = @MNO
    END
CLOSE CUR
DEALLOCATE CUR